<?php

/**
 * This is the model class for table "sps_wm_profil_sql".
 *
 * The followings are the available columns in table 'sps_wm_profil_sql':
 * @property integer $Benutzernummer
 * @property string $Passwort
 * @property string $Name
 * @property string $Vorname
 * @property string $Sprache
 * @property string $Geschlecht
 * @property string $Geburtsdatum
 * @property string $Strasse
 * @property integer $Hausnummer
 * @property string $Hausnummer_Zusatz
 * @property string $PLZ
 * @property string $Ortschaft
 * @property string $Kanton
 * @property string $Land
 * @property string $Telefon_Privat
 * @property string $Telefon_Geschaeft
 * @property string $Mobile
 * @property string $Email
 * @property string $Beruf_Schluessel
 * @property string $Beruf_als_Text
 * @property integer $Inaktiv
 *
 * The followings are the available model relations:
 * @property SpsMitgl001Sql $benutzernummer
 * @property SpsWmProfilZusatzSql $spsWmProfilZusatzSql
 */
class Member extends CActiveRecord {

	public $austritt_search;
	public $member_type_search;
	public $section_search;
	public $om_number_search;

	/**
	 * Returns the static model of the specified AR class.
	 * @param string $className active record class name.
	 * @return Member the static model class
	 */
	public static function model($className = __CLASS__) {
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName() {
		return 'sps_wm_profil_sql';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules() {
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('Benutzernummer, Hausnummer, Inaktiv', 'numerical', 'integerOnly' => true),
			array('Passwort', 'length', 'max' => 32),
			array('Name, Vorname, Strasse, Ortschaft, Kanton, Telefon_Privat, Telefon_Geschaeft, Mobile, Beruf_Schluessel', 'length', 'max' => 30),
			array('Sprache, Geschlecht', 'length', 'max' => 20),
			array('Geburtsdatum, Hausnummer_Zusatz', 'length', 'max' => 10),
			array('PLZ', 'length', 'max' => 12),
			array('Land', 'length', 'max' => 3),
			array('Email, Beruf_als_Text', 'length', 'max' => 50),
			// The following rule is used by search().
			// Please remove those attributes that should not be searched.
			array('Benutzernummer, Passwort, Name, Vorname, Sprache, Geschlecht, Geburtsdatum, Strasse, Hausnummer, Hausnummer_Zusatz, PLZ, Ortschaft, Kanton, Land, Telefon_Privat, Telefon_Geschaeft, Mobile, Email, Beruf_Schluessel, Beruf_als_Text, Inaktiv, austritt_search, member_type_search, section_search, om_number_search', 'safe', 'on' => 'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations() {
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'membership' => array(self::HAS_ONE, 'Membership', array('Benutzernummer_Web' => 'Benutzernummer'), 'joinType' => 'INNER JOIN', 'condition' => ' membership.Verband = \'JUSO Schweiz\' '),
			'memberships' => array(self::HAS_MANY, 'Membership', array('Benutzernummer_Web' => 'Benutzernummer'), 'joinType' => 'INNER JOIN', 'condition' => ' memberships.Verband = \'JUSO Schweiz\' '),
			'profileExtensions' => array(self::HAS_ONE, 'MemberExtensions', array('Benutzernummer' => 'Benutzernummer'), 'joinType' => 'INNER JOIN'),
			'membercodes' => array(self::HAS_MANY, 'MemberCode', 'Kundennummer', 'through' => 'membership', 'joinType' => 'INNER JOIN'),
			'codes' => array(self::HAS_MANY, 'Code', array('Codes_Gruppe' => 'Haupt_Code', 'Code' => 'Code'), 'through' => 'membercodes', 'joinType' => 'INNER JOIN', 'condition' => 'codes.Sprache = \'Deutsch\' AND Haupt_Code IN (7026,1059,1078)'),
		);
	}

//	public function scopes() {
//		return array(
//			'sektion' => array('condition' => "membership.Sektion = '" . Yii::app()->user->getState('sektion') . "'"),
//		);
//	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels() {
		return array(
			'Benutzernummer' => 'Benutzernummer',
			'Passwort' => 'Passwort',
			'Name' => 'Name',
			'Vorname' => 'Vorname',
			'Sprache' => 'Sprache',
			'Geschlecht' => 'Geschlecht',
			'Geburtsdatum' => 'Geburtsdatum',
			'Strasse' => 'Strasse',
			'Hausnummer' => 'Nr',
			'Hausnummer_Zusatz' => 'Hausnummer Zusatz',
			'PLZ' => 'PLZ',
			'Ortschaft' => 'Ort',
			'Kanton' => 'Kanton',
			'Land' => 'Land',
			'Telefon_Privat' => 'Telefon Privat',
			'Telefon_Geschaeft' => 'Telefon Geschaeft',
			'Mobile' => 'Mobile',
			'Email' => 'E-Mail',
			'Beruf_Schluessel' => 'Beruf Schluessel',
			'Beruf_als_Text' => 'Beruf Als Text',
			'Inaktiv' => 'Inaktiv',
			'austritt_search' => 'Austritt',
			'member_type_search' => 'Art',
			'section_search' => 'Sektion',
			'om_number_search' => 'OM-Nummer',
		);
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search($old = false) {

		$criteria = new CDbCriteria;

		$regionConstraint = "";
		if (Yii::app()->user->getState('roles') == 'admin') {
			$regionConstraint .= ' Mitgliedschaft_bei_Kundennr IN (SELECT id FROM sps_juso_sektionen) ';
		} else if (Yii::app()->user->getState('roles') == 'sektion') {
			$regionConstraint .= ' Mitgliedschaft_bei_Kundennr = \'' . Yii::app()->user->getState('sektion') . "'";
		} else {
			$regionConstraint .= ' Region = \'' . Yii::app()->user->getState('kanton') . "'";
		}

		$activeCondition = '';
		if ($old) {
			$activeCondition = 'Inaktiv = 0 AND (membership.Austritt != \'0000-00-00\') AND ';
		} else {
			$activeCondition = 'Inaktiv = 0 AND (membership.Austritt = \'0000-00-00\' OR membership.Austritt >= \'' . date('Y-m-d') . '\') AND ';
		}

		$criteria->with = array('membership' => array(
				'joinType' => 'INNER JOIN',
				'condition' => $activeCondition . $regionConstraint,
				'order' => 'Name ASC',
				));

		$austritt_search = '';
		$austritt_operator = '';
		if (preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/', $this->austritt_search, $matches)) {
			$austritt_operator = $matches[1];
			$austritt_search = $matches[2];
		}
		else
			$austritt_search = $this->austritt_search;

		$criteria->compare('Name', $this->Name, true);
		$criteria->compare('Vorname', $this->Vorname, true);
		$criteria->compare('Sprache', $this->Sprache, true);
		$criteria->compare('Geburtsdatum', $this->Geburtsdatum, true);
		$criteria->compare('Strasse', $this->Strasse, true);
		$criteria->compare('Hausnummer', $this->Hausnummer);
		$criteria->compare('Hausnummer_Zusatz', $this->Hausnummer_Zusatz, true);
		$criteria->compare('PLZ', $this->PLZ, true);
		$criteria->compare('Ortschaft', $this->Ortschaft, true);
		$criteria->compare('t.Kanton', $this->Kanton, true);
		$criteria->compare('Land', $this->Land, true);
		$criteria->compare('Telefon_Privat', $this->Telefon_Privat, true);
		$criteria->compare('Telefon_Geschaeft', $this->Telefon_Geschaeft, true);
		$criteria->compare('Mobile', $this->Mobile, true);
		$criteria->compare('Email', $this->Email, true);
		$criteria->compare('membership.Austritt', empty($this->austritt_search) ? '' : $austritt_operator . date('Y-m-d', strtotime($austritt_search)), true);
		$criteria->compare('membership.Klasse_Verband', $this->member_type_search, true);
		$criteria->compare('membership.Mitgliederbezeichnung', $this->section_search, false);
		$criteria->compare('membership.Kundennummer', $this->om_number_search, false);

		return new CActiveDataProvider(Member::model(), array(
					'criteria' => $criteria,
					'pagination' => array(
						'pageSize' => 20,
					),
				));
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function export($old = false) {
		$criteria = new CDbCriteria;

		$regionConstraint = "";
		if (Yii::app()->user->getState('roles') == 'admin') {
			$regionConstraint .= ' Verband = \'JUSO Schweiz\' ';
		} else if (Yii::app()->user->getState('roles') == 'sektion') {
			$regionConstraint .= ' Mitgliedschaft_bei_Kundennr = \'' . Yii::app()->user->getState('sektion') . "'";
		} else {
			$regionConstraint .= ' Region = \'' . Yii::app()->user->getState('kanton') . "'";
		}

		$activeCondition = '';
		if ($old) {
			$activeCondition = 'Inaktiv = 0 AND (membership.Austritt != \'0000-00-00\') AND ';
		} else {
			$activeCondition = 'Inaktiv = 0 AND (membership.Austritt = \'0000-00-00\' OR membership.Austritt >= \'' . date('Y-m-d') . '\') AND ';
		}

		$criteria->with = array('membership' => array(
				'joinType' => 'INNER JOIN',
				'condition' => $activeCondition . $regionConstraint,
				'order' => 'Name ASC',
				));

		$criteria->compare('Name', $this->Name, true);
		$criteria->compare('Vorname', $this->Vorname, true);
		$criteria->compare('Sprache', $this->Sprache, true);
		$criteria->compare('Geburtsdatum', $this->Geburtsdatum, true);
		$criteria->compare('Strasse', $this->Strasse, true);
		$criteria->compare('Hausnummer', $this->Hausnummer);
		$criteria->compare('Hausnummer_Zusatz', $this->Hausnummer_Zusatz, true);
		$criteria->compare('PLZ', $this->PLZ, true);
		$criteria->compare('Ortschaft', $this->Ortschaft, true);
		$criteria->compare('t.Kanton', $this->Kanton, true);
		$criteria->compare('Land', $this->Land, true);
		$criteria->compare('Telefon_Privat', $this->Telefon_Privat, true);
		$criteria->compare('Telefon_Geschaeft', $this->Telefon_Geschaeft, true);
		$criteria->compare('Mobile', $this->Mobile, true);
		$criteria->compare('Email', $this->Email, true);
		$criteria->compare('membership.Austritt', empty($this->austritt_search) ? '' : $austritt_operator . date('Y-m-d', strtotime($austritt_search)), true);
		$criteria->compare('membership.Klasse_Verband', $this->member_type_search, true);
		$criteria->compare('membership.Mitgliederbezeichnung', $this->section_search, false);
		$criteria->compare('membership.Kundennummer', $this->om_number_search, false);


		return new CActiveDataProvider(Member::model(), array(
					'criteria' => $criteria,
					'pagination' => array(
						'pageSize' => 10000,
					),
				));
	}

	public function isActive($memberId = false) {
		if ($memberId === false) {
			$memberId = $this->Benutzernummer;
		}

		$criteria = new CDbCriteria();
		$activeCondition = 'Inaktiv = 0 AND (membership.Austritt = \'0000-00-00\' OR membership.Austritt >= \'' . date('Y-m-d') . '\') ';

		$criteria->with = array('membership' => array(
				'joinType' => 'INNER JOIN',
				'condition' => $activeCondition,
				));

		if (Member::model()->findByPk($memberId, $criteria) !== null) {
			return true;
		}
		return false;
	}

	public function regionConstraint($strict = false) {

		$criteria = new CDbCriteria();
		if (Yii::app()->user->getState('roles') == 'kanton') {
			$criteria->condition .= 't.Benutzernummer IN (SELECT Benutzernummer_Web FROM sps_mitgl001_sql WHERE Region = \'' . Yii::app()->user->getState('kanton') . "') ";
			if ($strict) {
				$criteria->condition .= ' AND memberships.Mitgliednummer IN (SELECT Mitgliednummer FROM sps_mitgl001_sql WHERE Region = \'' . Yii::app()->user->getState('kanton') . "') ";
			}
		} else if (Yii::app()->user->getState('roles') == 'sektion') {
			$criteria->condition .= 't.Benutzernummer IN (SELECT Benutzernummer_Web FROM sps_mitgl001_sql WHERE Mitgliedschaft_bei_Kundennr = \'' . Yii::app()->user->getState('sektion') . "') ";
			if ($strict) {
				$criteria->condition .= '  AND memberships.Mitgliednummer IN (SELECT Mitgliednummer FROM sps_mitgl001_sql WHERE Mitgliedschaft_bei_Kundennr = \'' . Yii::app()->user->getState('sektion') . "') ";
			}
		}

		$this->getDbCriteria()->mergeWith($criteria);
		return $this;
	}

	public function membershipContstraint() {
		$criteria = new CDbCriteria();
		$criteria->condition .= 't.Benutzernummer IN (SELECT Benutzernummer_Web FROM sps_mitgl001_sql WHERE (Austritt = \'0000-00-00\' OR Austritt >= \'' . date('Y-m-d') . "')
			)";
		$this->getDbCriteria()->mergeWith($criteria);
		return $this;
	}

}